/*
 *	This file is part of Lounge-Cast.
 *
 *  Author:  	Markus Moormann
 *	E-mail:  	mmoorman@uni-osnabrueck.de
 *	Copyright:	University of Osnabrueck (Zentrum Virtuos)
 * 	Website:	www.uni-osnabrueck.de
 *	Date:		16.02.2011
 *
 *	Lounge-Cast is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *	Lounge-Cast is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Lounge-Cast.  If not, see <http://www.gnu.org/licenses/>.
 */
package backend.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import backend.dbConnection.ConnectionHelper;
import backend.vo.PlayerVO;
import backend.vo.PlaylistVO;

/**
 * class for player service
 *
 * @author Markus Moormann
 */
public class PlayerService {
	
	/**
	 * a logger to log errors, debug information, etc.
	 */
	private static Logger LOG = Logger.getLogger(PlayerService.class);
	/**
	 * the connection to database
	 */
	//private Connection connection;
	/**
	 * the statement so send to database
	 */
	//private Statement statement;
	
	/**
	 * get all players from database
	 * @return <code>List<PlayerVO></code> a list of PlayerVO
	 */
	public List<PlayerVO> getPlayer() {
		List<PlayerVO> result = new ArrayList<PlayerVO>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			String selectPlayer = 	"SELECT " +
										"`player_id`," +
										"`description`," +
										"`current_position` " +
									"FROM " +
										"`player` " +
									"ORDER BY " +
										"`description`";
			ResultSet rs = statement.executeQuery(selectPlayer);
			while(rs.next()) {
				PlayerVO tmp = new PlayerVO();
				tmp.player_id = rs.getString("player_id");
				tmp.description = rs.getString("description");
				tmp.position = rs.getFloat("current_position");
				result.add(tmp);
			}
		} catch (SQLException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return result;
	}
	
	/**
	 * save player to database
	 * 
	 * @param player the player to save
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean savePlayer(PlayerVO player) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String insertPlayer = 	"INSERT INTO " +
										"player " +
											"(" +
												"player_id," +
												"description" +
											")VALUES" +
											"(" +
												"'" + player.player_id + "'," +
												"'" + player.description + "'" +
											")";
			
			statement.execute(insertPlayer);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	public boolean deletePlayer(PlayerVO player) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String deletePlayer = 	"DELETE FROM " +
										"player " +
									"WHERE " +
										"player_id = '" + player.player_id + "'";
			
			statement.execute(deletePlayer);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * update a player in database
	 * 
	 * @param player the player to update
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updatePlayer(PlayerVO player) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String updatePlayer = 	"UPDATE " +
										"player " +
									"SET " +
										"description = '" + player.description + "' " +
									"WHERE " +
										"player_id = '" + player.player_id + "'";
			
			statement.execute(updatePlayer);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * add a playlist to a player
	 * 
	 * @param player the player to add the playlist to
	 * @param playlist the playlist to add
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean addPlaylistToPlayer(PlayerVO player, PlaylistVO playlist) {
		Connection connection = null;
		Statement statement = null;
		LOG.debug("Adding playlist " + playlist.playlist_id + " to player " + player.player_id);
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String addPlaylist = 	"INSERT INTO " +
										"`playlist_player` " +
									"SET " +
										"`playlist_id` = " + playlist.playlist_id + "," +
										"`player_id` = '" + player.player_id + "'";
			String updatePlayer = "UPDATE player SET last_update = NOW()";
			statement.execute(addPlaylist);
			statement.execute(updatePlayer);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}
	
	/**
	 * delete a playlist from player
	 * 
	 * @param player the player to delete the playlist from
	 * @param playlist the playlist to delete
	 * @return true on success, fals else
	 */
	public boolean deletePlaylistFromPlayer(PlayerVO player, PlaylistVO playlist) {
		Connection connection = null;
		Statement statement = null;
		LOG.debug("Deleting playlist " + playlist.playlist_id + " from player " + player.player_id);
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String deletePlaylist = "DELETE FROM " +
										"`playlist_player` " +
									"WHERE " +
										"`playlist_id` = '" + playlist.playlist_id + "' AND " +
										"`player_id` = '" + player.player_id + "'";
			String updatePlayer = "UPDATE player SET last_update = NOW()";
			statement.execute(deletePlaylist);
			statement.execute(updatePlayer);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(connection);
			ConnectionHelper.close(statement);
		}
		return true;
	}

}
